package com.kaifamiao.jdbc.meta;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class DbHelper {

    private static final String PREFIX = "jdbc.connection.";
    private static final String SUFFIX_DRIVER = ".driver";
    private static final String SUFFIX_URL = ".url";
    private static final String SUFFIX_USER = ".user";
    private static final String SUFFIX_PASSWORD = ".password";
    private static final String CONNECT = "connect";
    private static final String DEFAULT_CONNECT = "mysql" ;

    private final Properties props = new Properties();

    private String driverClassName;
    private String url;
    private String user;
    private String password;

    // 用来缓存数据库连接的实例变量
    private Connection conn;

    public static DbHelper getInstance(){
        return getInstance( "jdbc.properties" );
    }

    public static DbHelper getInstance( String filename ){
        DbHelper dh = new DbHelper();
        dh.read( filename );
        dh.parse();
        dh.load();
        dh.connect();
        return dh;
    }

    // 读取属性文件中的配置信息
    private void read( String filename ) {
        // 获得当前实例(this)对应的类的 Class实例
        Class<?> c = this.getClass();
        // 获得用于读取 类路径根目录("/") 下 指定文件(filename) 的字节输入流
        InputStream in = c.getResourceAsStream( "/" + filename );
        try {
            // 从字节输入流中加载 "属性名-属性值" 对 到 Properties集合中
            props.load(in);
            // 关闭字节输入流
            in.close();
        } catch ( IOException e ) {
            System.out.println( "读取资源文件时发生错误: " + e.getMessage() );
        }
    }

    // 从属性集获取相应属性值
    private void parse(){
        String connect = props.getProperty( CONNECT , DEFAULT_CONNECT);
        connect = connect.isBlank() ? DEFAULT_CONNECT : connect;
        driverClassName = props.getProperty(  PREFIX+ connect + SUFFIX_DRIVER );
        url = props.getProperty( PREFIX + connect + SUFFIX_URL );
        user = props.getProperty( PREFIX + connect + SUFFIX_USER );
        password = props.getProperty( PREFIX + connect + SUFFIX_PASSWORD );
    }

    // 加载并注册驱动
    private void load(){
        try {
            Class.forName( driverClassName );
        } catch (ClassNotFoundException cause) {
            String message = String.format( "驱动类%s不存在", driverClassName );
            throw new RuntimeException( message , cause );
        }
    }

    // 建立连接(并对实例变量conn初始化)
    private void connect() {
        try {
            conn = DriverManager.getConnection( url, user, password );
        } catch (SQLException cause) {
            String message = String.format( "数据库连接失败" );
            throw new RuntimeException( message , cause );
        }
    }

    // 创建Statement对象
    private Statement create(){
        try {
            Statement stmt = conn.createStatement();
            return stmt;
        } catch (SQLException cause) {
            throw new RuntimeException( "创建Statement失败" , cause);
        }
    }

    private List<Map<String,Object>> transform( ResultSet rs ){
        if( rs == null ) {
            throw new RuntimeException( "结果集为空" );
        }

        List<Map<String,Object>> list = new ArrayList<>();

        ResultSetMetaData meta ;
        try {
            meta = rs.getMetaData();
        } catch (SQLException cause) {
            throw new RuntimeException( "获取结果集元数据时发生错误" , cause );
        }

        try {
            int count = meta.getColumnCount();
            while (rs.next()) {
                Map<String,Object> map = new HashMap<>();
                for( int i = 1; i <= count ; i++ ){
                    String label = meta.getColumnLabel( i ); // 获取列名或别名
                    Object value = rs.getObject( i ); // 获取指定列的值
                    map.put( label, value );
                }
                list.add( map );
            }
            return list ;
        } catch ( SQLException cause ) {
            throw new RuntimeException( "从结果集获取数据时发生错误" , cause );
        }
    }

    // 执行查询并以 List<Map<String,Object>>形式返回结果集中的数据
    public List<Map<String,Object>> query(String sql) {
        // 将 sql 中所有英文字母转为大写
        String q = sql.toUpperCase();
        // 剔除字符串首尾空白
        q = q.trim();
        // 是否不以`SELECT`为前缀
        if( !q.startsWith( "SELECT" ) ) {
            throw new RuntimeException("你丫给定就不是查询语句");
        }
        Statement stmt = this.create();
        ResultSet rs = null;
        try {
            // 执行查询语句返回结果
            rs = stmt.executeQuery(sql);
            return this.transform( rs );
        } catch ( SQLException cause ) {
            throw new RuntimeException( "查询失败" , cause);
        } finally {
            this.release( rs );
            this.release( stmt );
        }
    }

    // 执行DML语句并返回受影响的记录数目
    public int execute( String sql ){
        // 将 sql 中所有英文字母转为大写
        String q = sql.toUpperCase();
        // 剔除字符串首尾空白
        q = q.trim();
        // 是否不以`INSERT`、`UPDATE`、`DELETE`为前缀
        if( !q.startsWith("INSERT") && !q.startsWith("UPDATE") && !q.startsWith("DELETE") ) {
            throw new RuntimeException("你丫给定就不是DML语句");
        }
        // 调用 当前实例 的 create 方法 创建 Statement对象
        Statement stmt = this.create();
        try {
            // "执行"SQL: 将DML语句发送到数据库服务器 并 获取数据库服务器返回的结果
            int count = stmt.executeUpdate(sql);
            // 返回结果
            return count;
        } catch ( SQLException cause ) {
            throw new RuntimeException( "" , cause );
        } finally {
            this.release( stmt );
        }
    }

    // 释放资源
    private void release( AutoCloseable ac ) {
        if( ac != null ) {
            try {
                ac.close();
            } catch (Exception cause) {
                String message = String.format( "释放资源时发生错误" );
                throw new RuntimeException( message , cause );
            }
        }
    }

    // 释放资源
    public void release() {
        this.release( conn );
    }
}
